package com.hbnu.sunjianwei.questionbank.dao;

import com.hbnu.sunjianwei.questionbank.entity.Question;
import com.hbnu.sunjianwei.questionbank.util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class QuestionDao {

    /**
     * 获取所有题目
     */
    public List<Question> getAllQuestions() {
        List<Question> questions = new ArrayList<>();
        String sql = "SELECT * FROM questions ORDER BY id";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                questions.add(resultSetToQuestion(rs));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return questions;
    }

    /**
     * 根据ID获取题目
     */
    public Question getQuestionById(int id) {
        String sql = "SELECT * FROM questions WHERE id = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return resultSetToQuestion(rs);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 将ResultSet转换为Question对象
     */
    private Question resultSetToQuestion(ResultSet rs) throws SQLException {
        Question question = new Question();
        question.setId(rs.getInt("id"));
        question.setQuestionText(rs.getString("question_text"));
        question.setOptionA(rs.getString("option_a"));
        question.setOptionB(rs.getString("option_b"));
        question.setOptionC(rs.getString("option_c"));
        question.setOptionD(rs.getString("option_d"));
        question.setCorrectAnswer(rs.getString("correct_answer"));
        question.setQuestionType(rs.getString("question_type"));
        question.setDifficulty(rs.getString("difficulty"));
        question.setSubject(rs.getString("subject"));
        return question;
    }

    /**
     * 测试数据库连接
     */
    public boolean testConnection() {
        try (Connection conn = DBUtil.getConnection()) {
            return conn != null && !conn.isClosed();
        } catch (Exception e) {
            System.err.println("数据库连接测试失败: " + e.getMessage());
            return false;
        }
    }

    /**
     * 获取题目总数
     */
    public int getQuestionCount() {
        String sql = "SELECT COUNT(*) FROM questions";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 按科目统计题目数量
     */
    public void showQuestionStats() {
        String sql = "SELECT subject, COUNT(*) as count FROM questions GROUP BY subject";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            System.out.println("\n=== 题目统计 ===");
            while (rs.next()) {
                String subject = rs.getString("subject");
                int count = rs.getInt("count");
                System.out.println(subject + ": " + count + " 题");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 添加新题目
     */
    public boolean addQuestion(Question question) {
        String sql = "INSERT INTO questions (question_text, option_a, option_b, option_c, option_d, " +
                "correct_answer, question_type, difficulty, subject) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, question.getQuestionText());
            stmt.setString(2, question.getOptionA());
            stmt.setString(3, question.getOptionB());
            stmt.setString(4, question.getOptionC());
            stmt.setString(5, question.getOptionD());
            stmt.setString(6, question.getCorrectAnswer());
            stmt.setString(7, question.getQuestionType());
            stmt.setString(8, question.getDifficulty());
            stmt.setString(9, question.getSubject());

            int rows = stmt.executeUpdate();
            return rows > 0;
        } catch (Exception e) {
            System.err.println("添加题目失败: " + e.getMessage());
            return false;
        }
    }

    /**
     * 随机获取一道题目
     */
    public Question getRandomQuestion() {
        String sql = "SELECT * FROM questions ORDER BY RAND() LIMIT 1";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            if (rs.next()) {
                return resultSetToQuestion(rs);
            }
        } catch (Exception e) {
            System.err.println("获取随机题目失败: " + e.getMessage());
        }
        return null;
    }

    /**
     * 按科目随机获取一道题目
     */
    public Question getRandomQuestionBySubject(String subject) {
        String sql = "SELECT * FROM questions WHERE subject = ? ORDER BY RAND() LIMIT 1";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, subject);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return resultSetToQuestion(rs);
                }
            }
        } catch (Exception e) {
            System.err.println("按科目获取随机题目失败: " + e.getMessage());
        }
        return null;
    }

    /**
     * 获取所有科目列表
     */
    public List<String> getAllSubjects() {
        List<String> subjects = new ArrayList<>();
        String sql = "SELECT DISTINCT subject FROM questions WHERE subject IS NOT NULL";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()) {

            while (rs.next()) {
                subjects.add(rs.getString("subject"));
            }
        } catch (Exception e) {
            System.err.println("获取科目列表失败: " + e.getMessage());
        }
        return subjects;
    }
    /**
     * 随机获取指定数量的题目
     */
    public List<Question> getRandomQuestions(int count) {
        List<Question> questions = new ArrayList<>();
        String sql = "SELECT * FROM questions ORDER BY RAND() LIMIT ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, count);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    questions.add(resultSetToQuestion(rs));
                }
            }
        } catch (Exception e) {
            System.err.println("获取随机题目列表失败: " + e.getMessage());
        }
        return questions;
    }

    /**
     * 按科目随机获取指定数量的题目
     */
    public List<Question> getRandomQuestionsBySubject(String subject, int count) {
        List<Question> questions = new ArrayList<>();
        String sql = "SELECT * FROM questions WHERE subject = ? ORDER BY RAND() LIMIT ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, subject);
            stmt.setInt(2, count);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    questions.add(resultSetToQuestion(rs));
                }
            }
        } catch (Exception e) {
            System.err.println("按科目获取随机题目列表失败: " + e.getMessage());
        }
        return questions;
    }

    /**
     * 按难度随机获取指定数量的题目
     */
    public List<Question> getRandomQuestionsByDifficulty(String difficulty, int count) {
        List<Question> questions = new ArrayList<>();
        String sql = "SELECT * FROM questions WHERE difficulty = ? ORDER BY RAND() LIMIT ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, difficulty);
            stmt.setInt(2, count);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    questions.add(resultSetToQuestion(rs));
                }
            }
        } catch (Exception e) {
            System.err.println("按难度获取随机题目列表失败: " + e.getMessage());
        }
        return questions;
    }

    /**
     * 检查题库中是否有足够题目
     */
    public boolean hasEnoughQuestions(int count) {
        return getQuestionCount() >= count;
    }

    /**
     * 检查指定科目下是否有足够题目
     */
    public boolean hasEnoughQuestionsBySubject(String subject, int count) {
        String sql = "SELECT COUNT(*) FROM questions WHERE subject = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, subject);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) >= count;
                }
            }
        } catch (Exception e) {
            System.err.println("检查题目数量失败: " + e.getMessage());
        }
        return false;
    }

    /**
     * 检查指定难度下是否有足够题目
     */
    public boolean hasEnoughQuestionsByDifficulty(String difficulty, int count) {
        String sql = "SELECT COUNT(*) FROM questions WHERE difficulty = ?";

        try (Connection conn = DBUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, difficulty);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) >= count;
                }
            }
        } catch (Exception e) {
            System.err.println("检查题目数量失败: " + e.getMessage());
        }
        return false;
    }
}